using Dapper;
using MediatR;
using Microsoft.Data.SqlClient;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using RBAC.Domain;
using RBAC.Domain.SalesManage;
using RBAC.ErrorCode;
using RBAC.Interstructrue.Base;
using RBAC.Write.Api.Application.Command.ERP.SalesManagement.Billing;
using System.Data;
using System.Linq;

namespace RBAC.Write.Api.Application.Handler.ERP.SalesManagement.Billing
{
    /// <summary>
    /// 开票通知单导出处理器
    /// 支持单据模式和明细模式两种导出方式
    /// </summary>
    public class ExportBillingNoticeHandler : IRequestHandler<ExportBillingNoticeCommand, APIResult<byte[]>>
    {
        private readonly IBaseRepository<BillingAccounting> _repository;
        private readonly IDbConnectionFactory _connectionFactory;
        private readonly ILogger<ExportBillingNoticeHandler> _logger;

        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="repository">开票通知单仓储</param>
        /// <param name="connectionFactory">数据库连接工厂</param>
        /// <param name="logger">日志记录器</param>
        public ExportBillingNoticeHandler(
            IBaseRepository<BillingAccounting> repository,
            IDbConnectionFactory connectionFactory,
            ILogger<ExportBillingNoticeHandler> logger)
        {
            _repository = repository;
            _connectionFactory = connectionFactory;
            _logger = logger;
        }

        /// <summary>
        /// 处理开票通知单导出请求
        /// </summary>
        /// <param name="request">导出请求参数</param>
        /// <param name="cancellationToken">取消令牌</param>
        /// <returns>Excel文件字节数组</returns>
        public async Task<APIResult<byte[]>> Handle(ExportBillingNoticeCommand request, CancellationToken cancellationToken)
        {
            try
            {
                _logger.LogInformation("开始导出开票通知单，导出类型：{ExportType}，参数：{@Request}", 
                    request.ExportType == 1 ? "单据模式" : "明细模式", request);

                // 验证导出数量限制
                if (request.PageSize > 10000 && !request.ExportAll)
                {
                    return new APIResult<byte[]>
                    {
                        Code = APIEnums.Error,
                        Msg = "单次导出数量不能超过10000条，如需导出更多请设置ExportAll=true或分批导出"
                    };
                }

                byte[] excelBytes;

                // 根据导出类型执行不同的导出逻辑
                if (request.ExportType == 1)
                {
                    // 单据模式导出
                    excelBytes = await ExportBillingNoticeMode(request, cancellationToken);
                }
                else
                {
                    // 明细模式导出
                    excelBytes = await ExportBillingDetailMode(request, cancellationToken);
                }

                if (excelBytes == null || excelBytes.Length == 0)
                {
                    return new APIResult<byte[]>
                    {
                        Code = APIEnums.Error,
                        Msg = "没有可导出的数据"
                    };
                }

                _logger.LogInformation("开票通知单导出完成，文件大小：{FileSize} bytes", excelBytes.Length);

                return new APIResult<byte[]>
                {
                    Code = APIEnums.Success,
                    Data = excelBytes
                };
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "导出开票通知单失败：{Message}", ex.Message);
                return new APIResult<byte[]>
                {
                    Code = APIEnums.Error,
                    Msg = $"导出失败：{ex.Message}"
                };
            }
        }

        /// <summary>
        /// 导出单据模式数据
        /// </summary>
        /// <param name="request">导出请求</param>
        /// <param name="cancellationToken">取消令牌</param>
        /// <returns>Excel文件字节数组</returns>
        private async Task<byte[]> ExportBillingNoticeMode(ExportBillingNoticeCommand request, CancellationToken cancellationToken)
        {
            using var connection = _connectionFactory.CreateConnection();
            
            var sql = @"
                SELECT 
                    ba.BillNumber,
                    ba.BusinessType,
                    ba.CustomerName,
                    ba.BankName,
                    ba.DeliveryEmail,
                    ba.BillDate,
                    ba.Operator,
                    ba.InvoiceType,
                    ba.ProductCode,
                    ba.RedReason,
                    ba.Remarks,
                    ba.BillStatus,
                    ba.Reviewer,
                    ba.TaxRate,
                    ba.TaxId,
                    ba.BusinessStatus,
                    ba.ReviewDate,
                    ba.InvoiceCategory,
                    ba.AddressPhone,
                    IFNULL(SUM(bm.Amount), 0) AS TotalAmount
                FROM BillingAccounting ba
                LEFT JOIN BillingMaterials bm ON ba.Id = bm.BillingAccountingId
                WHERE ba.IsDeleted = 0
            ";

            var parameters = new DynamicParameters();

            // 添加查询条件
            if (!string.IsNullOrWhiteSpace(request.BillNo))
            {
                sql += " AND ba.BillNumber LIKE @BillNo";
                parameters.Add("BillNo", $"%{request.BillNo}%");
            }

            if (!string.IsNullOrWhiteSpace(request.CustomerName))
            {
                sql += " AND ba.CustomerName LIKE @CustomerName";
                parameters.Add("CustomerName", $"%{request.CustomerName}%");
            }

            if (!string.IsNullOrWhiteSpace(request.InvoiceName))
            {
                sql += " AND ba.InvoiceName LIKE @InvoiceName";
                parameters.Add("InvoiceName", $"%{request.InvoiceName}%");
            }

            if (request.StartDate.HasValue)
            {
                sql += " AND ba.BillDate >= @StartDate";
                parameters.Add("StartDate", request.StartDate.Value);
            }

            if (request.EndDate.HasValue)
            {
                sql += " AND ba.BillDate <= @EndDate";
                parameters.Add("EndDate", request.EndDate.Value);
            }

            sql += " GROUP BY ba.Id, ba.BillNumber, ba.BusinessType, ba.CustomerName, ba.BankName, ba.DeliveryEmail, ba.BillDate, ba.Operator, ba.InvoiceType, ba.ProductCode, ba.RedReason, ba.Remarks, ba.BillStatus, ba.Reviewer, ba.TaxRate, ba.TaxId, ba.BusinessStatus, ba.ReviewDate, ba.InvoiceCategory, ba.AddressPhone";

            // 添加分页（仅当不导出全部数据时）
            if (!request.ExportAll)
            {
                sql += @"
                    ORDER BY ba.BillDate DESC, ba.Id DESC
                    LIMIT @Offset, @PageSize
                ";
                parameters.Add("Offset", (request.PageIndex - 1) * request.PageSize);
                parameters.Add("PageSize", request.PageSize);
            }
            else
            {
                sql += " ORDER BY ba.BillDate DESC, ba.Id DESC";
            }

            var data = await connection.QueryAsync<BillingNoticeExportDto>(sql, parameters);

            if (!data.Any())
            {
                return Array.Empty<byte>();
            }

            return GenerateBillingNoticeExcel(data.ToList());
        }

        /// <summary>
        /// 导出明细模式数据
        /// </summary>
        /// <param name="request">导出请求</param>
        /// <param name="cancellationToken">取消令牌</param>
        /// <returns>Excel文件字节数组</returns>
        private async Task<byte[]> ExportBillingDetailMode(ExportBillingNoticeCommand request, CancellationToken cancellationToken)
        {
            using var connection = _connectionFactory.CreateConnection();

            var sql = @"
                SELECT 
                    ba.BillNumber,
                    ba.CustomerName,
                    ba.BillDate,
                    bm.OrderNumber,
                    bm.MaterialCode,
                    bm.MaterialName,
                    bm.Specification,
                    bm.Unit,
                    bm.Quantity,
                    bm.UnitPrice,
                    bm.Amount,
                    bm.InvoiceName,
                    bm.Remarks,
                    ba.BillStatus
                FROM BillingAccounting ba
                INNER JOIN BillingMaterials bm ON ba.Id = bm.BillingAccountingId
                WHERE ba.IsDeleted = 0 AND bm.IsDeleted = 0
            ";

            var parameters = new DynamicParameters();

            // 添加查询条件
            if (!string.IsNullOrWhiteSpace(request.BillNo))
            {
                sql += " AND ba.BillNumber LIKE @BillNo";
                parameters.Add("BillNo", $"%{request.BillNo}%");
            }

            if (!string.IsNullOrWhiteSpace(request.CustomerName))
            {
                sql += " AND ba.CustomerName LIKE @CustomerName";
                parameters.Add("CustomerName", $"%{request.CustomerName}%");
            }

            if (!string.IsNullOrWhiteSpace(request.InvoiceName))
            {
                sql += " AND bm.InvoiceName LIKE @InvoiceName";
                parameters.Add("InvoiceName", $"%{request.InvoiceName}%");
            }

            if (request.StartDate.HasValue)
            {
                sql += " AND ba.BillDate >= @StartDate";
                parameters.Add("StartDate", request.StartDate.Value);
            }

            if (request.EndDate.HasValue)
            {
                sql += " AND ba.BillDate <= @EndDate";
                parameters.Add("EndDate", request.EndDate.Value);
            }

            // 添加分页（仅当不导出全部数据时）
            if (!request.ExportAll)
            {
                sql += @"
                    ORDER BY ba.BillDate DESC, ba.Id DESC, bm.Id
                    LIMIT @Offset, @PageSize
                ";
                parameters.Add("Offset", (request.PageIndex - 1) * request.PageSize);
                parameters.Add("PageSize", request.PageSize);
            }
            else
            {
                sql += " ORDER BY ba.BillDate DESC, ba.Id DESC, bm.Id";
            }

            var data = await connection.QueryAsync<BillingDetailExportDto>(sql, parameters);

            if (!data.Any())
            {
                return Array.Empty<byte>();
            }

            return GenerateBillingDetailExcel(data.ToList());
        }

        /// <summary>
        /// 生成单据模式Excel文件
        /// </summary>
        /// <param name="data">单据数据</param>
        /// <returns>Excel文件字节数组</returns>
        private byte[] GenerateBillingNoticeExcel(List<BillingNoticeExportDto> data)
        {
            var workbook = new XSSFWorkbook();
            var sheet = workbook.CreateSheet("开票通知单");

            // 创建标题行样式
            var headerStyle = workbook.CreateCellStyle();
            var headerFont = workbook.CreateFont();
            headerFont.IsBold = true;
            headerStyle.SetFont(headerFont);

            // 定义表头
            var headers = new[]
            {
                "单据编号", "业务类型", "客户名称", "开户行",
                "交付邮箱", "单据日期", "操作员", "发票类型", "银行账号",
                "红字原因", "备注信息", "单据状态", "审批员", "税率",
                "税号", "业务状态", "审批日期", "发票分类", "地址电话", "合计金额"
            };

            // 创建标题行
            var headerRow = sheet.CreateRow(0);
            for (int i = 0; i < headers.Length; i++)
            {
                var cell = headerRow.CreateCell(i);
                cell.SetCellValue(headers[i]);
                cell.CellStyle = headerStyle;
            }

            // 填充数据
            for (int i = 0; i < data.Count; i++)
            {
                var row = sheet.CreateRow(i + 1);
                var item = data[i];

                row.CreateCell(0).SetCellValue(item.BillNumber ?? "");
                row.CreateCell(1).SetCellValue(item.BusinessType ?? "");
                row.CreateCell(2).SetCellValue(item.CustomerName ?? "");
                row.CreateCell(3).SetCellValue(item.BankName ?? "");
                row.CreateCell(4).SetCellValue(item.DeliveryEmail ?? "");
                row.CreateCell(5).SetCellValue(item.BillDate?.ToString("yyyy-MM-dd") ?? "");
                row.CreateCell(6).SetCellValue(item.Operator ?? "");
                row.CreateCell(7).SetCellValue(item.InvoiceType ?? "");
                row.CreateCell(8).SetCellValue(item.ProductCode ?? "");
                row.CreateCell(9).SetCellValue(item.RedReason ?? "");
                row.CreateCell(10).SetCellValue(item.Remarks ?? "");
                row.CreateCell(11).SetCellValue(item.BillStatus ?? "");
                row.CreateCell(12).SetCellValue(item.Reviewer ?? "");
                row.CreateCell(13).SetCellValue(item.TaxRate?.ToString("F2") ?? "");
                row.CreateCell(14).SetCellValue(item.TaxId ?? "");
                row.CreateCell(15).SetCellValue(item.BusinessStatus ?? "");
                row.CreateCell(16).SetCellValue(item.ReviewDate?.ToString("yyyy-MM-dd") ?? "");
                row.CreateCell(17).SetCellValue(item.InvoiceCategory ?? "");
                row.CreateCell(18).SetCellValue(item.AddressPhone ?? "");
                row.CreateCell(19).SetCellValue(item.TotalAmount.ToString("F2"));
            }

            // 自动调整列宽
            for (int i = 0; i < headers.Length; i++)
            {
                sheet.AutoSizeColumn(i);
            }

            using var memoryStream = new MemoryStream();
            workbook.Write(memoryStream);
            return memoryStream.ToArray();
        }

        /// <summary>
        /// 生成明细模式Excel文件
        /// </summary>
        /// <param name="data">明细数据</param>
        /// <returns>Excel文件字节数组</returns>
        private byte[] GenerateBillingDetailExcel(List<BillingDetailExportDto> data)
        {
            var workbook = new XSSFWorkbook();
            var sheet = workbook.CreateSheet("开票明细");

            // 创建标题行样式
            var headerStyle = workbook.CreateCellStyle();
            var headerFont = workbook.CreateFont();
            headerFont.IsBold = true;
            headerStyle.SetFont(headerFont);

            // 定义表头
            var headers = new[]
            {
                "单据编号", "客户名称", "单据日期", "销售订单号", "物料编码",
                "物料名称", "规格型号", "主计量单位", "数量", "单价",
                "金额", "开票名称", "备注", "单据状态"
            };

            // 创建标题行
            var headerRow = sheet.CreateRow(0);
            for (int i = 0; i < headers.Length; i++)
            {
                var cell = headerRow.CreateCell(i);
                cell.SetCellValue(headers[i]);
                cell.CellStyle = headerStyle;
            }

            // 填充数据
            for (int i = 0; i < data.Count; i++)
            {
                var row = sheet.CreateRow(i + 1);
                var item = data[i];

                row.CreateCell(0).SetCellValue(item.BillNumber ?? "");
                row.CreateCell(1).SetCellValue(item.CustomerName ?? "");
                row.CreateCell(2).SetCellValue(item.BillDate?.ToString("yyyy-MM-dd") ?? "");
                row.CreateCell(3).SetCellValue(item.OrderNumber ?? "");
                row.CreateCell(4).SetCellValue(item.MaterialCode ?? "");
                row.CreateCell(5).SetCellValue(item.MaterialName ?? "");
                row.CreateCell(6).SetCellValue(item.Specification ?? "");
                row.CreateCell(7).SetCellValue(item.Unit ?? "");
                row.CreateCell(8).SetCellValue(item.Quantity?.ToString() ?? "");
                row.CreateCell(9).SetCellValue(item.UnitPrice?.ToString("F2") ?? "");
                row.CreateCell(10).SetCellValue(item.Amount?.ToString("F2") ?? "");
                row.CreateCell(11).SetCellValue(item.InvoiceName ?? "");
                row.CreateCell(12).SetCellValue(item.Remarks ?? "");
                row.CreateCell(13).SetCellValue(item.BillStatus ?? "");
            }

            // 自动调整列宽
            for (int i = 0; i < headers.Length; i++)
            {
                sheet.AutoSizeColumn(i);
            }

            using var memoryStream = new MemoryStream();
            workbook.Write(memoryStream);
            return memoryStream.ToArray();
        }
    }
}